﻿//**************************************************************************//
// Copyright (C) Abdul Rasheed. All rights Reserved.                        //
// rasheedat.blogspot.com                                                   //
//**************************************************************************//
using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using PPHR.Common;
using PPHR.DataAccess;

namespace PPHR.DataLogic
{
    /// <summary>
    /// All personal detail related queries stored here
    /// </summary>
    public class PersonalDetailLogic
    {
        CommonDataLogic commonLogic = new CommonDataLogic();
        DBConnection dbCon = new DBConnection();

        /// <summary>
        /// Update personal details in database
        /// </summary>
        /// <param name="PID">Personal details</param>
        public void UpdateDetail(PersonalData PID)
        {
            try
            {
                // Remove Personal Details
                string sql = string.Empty;
                int result = 0;
                sql = "Delete from Addresses";
                result = dbCon.ExecuteNonQuery(sql);
                sql = "Delete from OtherIDs";
                result = dbCon.ExecuteNonQuery(sql);
                sql = "Delete from PersonalDetail";
                result = dbCon.ExecuteNonQuery(sql);

                // Insert - Personal detail
                sql = "Insert into PersonalDetail Values(";
                sql += "'" + PID.PatientID + "', ";
                sql += "'" + commonLogic.replaceInjectionString(PID.Name) + "', ";
                sql += "'" + PID.DOB + "', ";
                sql += "'" + PID.Gender + "', ";
                sql += "'" + PID.MartialStatus + "', ";
                sql += "'" + commonLogic.replaceInjectionString(PID.SSNumber) + "', ";
                sql += "'" + commonLogic.replaceInjectionString(PID.DrivingLicenseNumber) + "', ";
                sql += "'" + PID.Nationality + "', ";
                sql += "'" + commonLogic.replaceInjectionString(PID.AlergicTo) + "', ";
                sql += "'" + commonLogic.replaceInjectionString(PID.BloodGroup) + "', ";
                sql += "'" + commonLogic.replaceInjectionString(PID.MotherName) + "', ";
                sql += "'" + commonLogic.replaceInjectionString(PID.AliasName) + "', ";
                sql += "'" + commonLogic.replaceInjectionString(PID.PrimaryLanguage) + "', ";
                sql += "'" + DateTime.Now.ToString() + "', ";
                sql += "'" + DateTime.Now.ToString() + "'";
                sql += ")";
                result = dbCon.ExecuteNonQuery(sql);

                // Insert - Address
                foreach (Address adress in PID.Addresses)
                {
                    //Address adress = PID.Addresses[i];
                    sql = "Insert into Addresses Values(";
                    sql += "'" + PID.PatientID + "', ";
                    sql += "'" + adress.AddressType + "', ";
                    sql += "'" + commonLogic.replaceInjectionString(adress.Street1) + "', ";
                    sql += "'" + commonLogic.replaceInjectionString(adress.Street2) + "', ";
                    sql += "'" + commonLogic.replaceInjectionString(adress.City) + "', ";
                    sql += "'" + commonLogic.replaceInjectionString(adress.State) + "', ";
                    sql += "'" + commonLogic.replaceInjectionString(adress.Zip) + "', ";
                    sql += "'" + commonLogic.replaceInjectionString(adress.Country) + "', ";
                    sql += "'" + commonLogic.replaceInjectionString(adress.Fax) + "', ";
                    sql += "'" + commonLogic.replaceInjectionString(adress.Phone) + "', ";
                    sql += "'" + DateTime.Now.ToString() + "', ";
                    sql += "'" + DateTime.Now.ToString() + "'";
                    sql += ")";
                    result = dbCon.ExecuteNonQuery(sql);
                }

                // Other Ids
                foreach (IDs otherIDs in PID.OtherIDs)
                {
                    sql = "Insert into OtherIds Values(";
                    sql += "'" + PID.PatientID + "', ";
                    sql += "'" + commonLogic.replaceInjectionString(otherIDs.Hospital) + "', ";
                    sql += "'" + commonLogic.replaceInjectionString(otherIDs.HospitalID) + "',";
                    sql += "'" + DateTime.Now.ToString() + "', ";
                    sql += "'" + DateTime.Now.ToString() + "'";
                    sql += ")";
                    result = dbCon.ExecuteNonQuery(sql);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.InnerException.ToString(), ex);
            }
        }
        
        /// <summary>
        /// Ge the personal details from the database
        /// </summary>
        /// <returns></returns>
        public PersonalData GetPersonalData()
        {
            PersonalData PID = null;
            try
            {
                string sql = "Select * From PersonalDetail";
                DataTable dtPersonal = dbCon.ExecuteQueryAndGetDataTable(sql);
                if (dtPersonal != null && dtPersonal.Rows.Count > 0)
                {
                    PID = new PersonalData();
                    PID.PatientID = dtPersonal.Rows[0]["PatientID"].ToString();
                    PID.Name = dtPersonal.Rows[0]["Name"].ToString();
                    PID.DOB = DateTime.Parse(dtPersonal.Rows[0]["DOB"].ToString());
                    PID.Gender = dtPersonal.Rows[0]["Gender"].ToString();
                    PID.MartialStatus = dtPersonal.Rows[0]["MartialStatus"].ToString();
                    PID.SSNumber = dtPersonal.Rows[0]["SSNumber"].ToString();
                    PID.DrivingLicenseNumber = dtPersonal.Rows[0]["DrivingLicenseNumber"].ToString();
                    PID.Nationality = dtPersonal.Rows[0]["Nationality"].ToString();
                    PID.AlergicTo = dtPersonal.Rows[0]["AlergicTo"].ToString();
                    PID.BloodGroup = dtPersonal.Rows[0]["BloodGroup"].ToString();
                    PID.MotherName = dtPersonal.Rows[0]["MotherName"].ToString();
                    PID.AliasName = dtPersonal.Rows[0]["AliasName"].ToString();
                    PID.PrimaryLanguage = dtPersonal.Rows[0]["PrimaryLanguage"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.InnerException.ToString(), ex);
            }
            return PID;
        }

        /// <summary>
        /// Get Other Ids
        /// </summary>
        /// <returns></returns>
        public DataTable GetPersonOtherIDs()
        {
            DataTable dt = null;
            try
            {
                string sql = "Select HospitalName as Name, HospitalID As ID  From OtherIds";
                dt = dbCon.ExecuteQueryAndGetDataTable(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.InnerException.ToString(), ex);
            }
            return dt;
        }

        /// <summary>
        /// Get address
        /// </summary>
        /// <returns></returns>
        public DataTable GetPersonAddress()
        {
            DataTable dt = null;
            try
            {
                string sql = "SELECT Addresses.PatientID, Addresses.AddressType, MasterCodes.Description AS Type, Addresses.Street1, Addresses.Street2, Addresses.City,";
                sql += " Addresses.State, Addresses.Zip, Addresses.Country, Addresses.Fax, Addresses.Phone";
                sql += " FROM  Addresses INNER JOIN";
                sql += "       MasterCodes ";
                sql += " ON Addresses.AddressType = MasterCodes.Code";
                sql += " WHERE     (MasterCodes.CodeGroup = 'ADDRTYPE')";
                dt = dbCon.ExecuteQueryAndGetDataTable(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.InnerException.ToString(), ex);
            }
            return dt;
        }
    }
}
